12. 文本 + 练习：WITH 与子查询

你的第一个 WITH (CTE)

下面是“你的第一个子查询”部分的问题和解决方案。

问题： 你需要算出每个渠道每天的平均事件数。

解决方案： 

SELECT channel, AVG(events) AS average_events
FROM (SELECT DATE_TRUNC('day',occurred_at) AS day,
channel, COUNT(*) as events
FROM web_events
GROUP BY 1,2) sub
GROUP BY channel
ORDER BY 2 DESC;

我们使用 WITH 语句重新编写查询。

注意：你可以获取内部查询： 

SELECT DATE_TRUNC('day',occurred_at) AS day,
channel, COUNT(*) as events
FROM web_events
GROUP BY 1,2

我们在此部分放入 WITH 语句。注意，在下面我们将表格的别名设为 events ： 

WITH events AS (
SELECT DATE_TRUNC('day',occurred_at) AS day,
channel, COUNT(*) as events
FROM web_events
GROUP BY 1,2)

现在，我们可以像对待数据库中的任何其他表格一样使用这个新创建的 events 表格： 

WITH events AS (
SELECT DATE_TRUNC('day',occurred_at) AS day,
channel, COUNT(*) as events
FROM web_events
GROUP BY 1,2)

SELECT channel, AVG(events) AS average_events
FROM events
GROUP BY channel
ORDER BY 2 DESC;

对于上述示例，我们只需一个额外的表格，但是想象下我们要创建第二个表格来从中获取数据。我们可以按照以下方式来创建额外的表格并从中获取数据： 

WITH table1 AS (
SELECT *
FROM web_events),

table2 AS (
SELECT *
FROM accounts)


SELECT *
FROM table1
JOIN table2
ON table1.account_id = table2.id;

然后，你可以按照相同的方式使用 WITH 语句添加越来越多的表格。底部的练习将确保你掌握了这些新查询的所有必要组成部分。

Workspace

This section contains either a workspace (it can be a Jupyter Notebook workspace or an online code editor work space, etc.) and it cannot be automatically downloaded to be generated here. Please access the classroom with your account and manually download the workspace to your local machine. Note that for some courses, Udacity upload the workspace files onto https://github.com/udacity , so you may be able to download them there.

Workspace Information:

  • Default file path:
  • Workspace type: sql-evaluator
  • Opened files (when workspace is loaded): n/a

选中以下关于 WITH 语句的所有正确描述。

SOLUTION:
  • 在使用 **WITH** 创建多个表格时，需要在每个表格后面加一个逗号，但是在引向最终查询的最后一个表格后面不需添加。
  • 新表格名称始终使用 `table_name AS` 设置别名，后面是位于小括号中的查询。

Next Concept